package org.mytool.common.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.mytool.cache.CacheKey;
import org.mytool.cache.Cacheable;
import org.mytool.cache.Cacheevict;
import org.mytool.cache.KeyMode;

import javax.sql.DataSource;
import javax.xml.crypto.Data;

public class MysqlTableInfo implements TableInfoI
{
    
//    private Connection connection = null;直接数据连接，发挥不了数据源的作用并且，连接超时就完蛋了
    private DataSource dataSource;
    // static String url;
    // static String username;
    // static String password;
    private String dbName;
    
    public String getDbName() {
		return dbName;
	}

	public void setDbName(String dbName) {
		this.dbName = dbName;
	}

	static String rt = "\r\t";
    
    String SQLTables = "show tables";
    
    private String method;
    
    private String argv;
    
    static String selectStr = "select ";
    
    static String from = " from ";
    
    /*
     * public void setConnInfo(String url, String username, String password) throws SQLException { // TODO
     * Auto-generated method stub this.url = url; this.username = username; this.password = password; this.dbName =
     * getDbNameByurl(url); //this.connection = DriverManager.getConnection(url, username, password); }
     */
    
    public void setDbNameByUrl(String url)
    {
        this.dbName = getDbNameByurl(url);
    }
    
   /* public void setConnection(Connection connection)
    {
        // TODO Auto-generated method stub
        this.connection = connection;
    }*/

    public void setDataSource(DataSource dataSource){
        this.dataSource = dataSource;
    }
    
    public Connection getConnection()
    {
        // return //DBUtils.getConnection(url,username,password);
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return  null;
    }
    
    public List<String> getTables()
        throws SQLException
    {
        Connection con = getConnection();
        PreparedStatement ps = con.prepareStatement(this.SQLTables);
        ResultSet rs = ps.executeQuery();
        List list = new ArrayList();
        while (rs.next())
        {
            String tableName = rs.getString(1);
            list.add(tableName);
        }
        // DBUtils.free(rs, ps, con);
        return list;
    }
    
    
    public List<ColumnData> getColumnDatas(String tableName)
        throws SQLException
    {
        String SQLColumns =
            "select column_name ,data_type,column_comment,0,0,character_maximum_length,is_nullable nullable,column_key from information_schema.columns where table_name =  '"
                + tableName + "' " + "and table_schema =  '" + dbName + "'";
                
        Connection con = getConnection();
        PreparedStatement ps = con.prepareStatement(SQLColumns);
        List<ColumnData> columnList = new ArrayList();
        ResultSet rs = ps.executeQuery();
        StringBuffer str = new StringBuffer();
        StringBuffer getset = new StringBuffer();
        while (rs.next())
        {
            String name = rs.getString(1).toLowerCase();
            String type = rs.getString(2);
            String comment = rs.getString(3);
            String precision = rs.getString(4);
            String scale = rs.getString(5);
            String charmaxLength = rs.getString(6) == null ? "" : rs.getString(6);
            String nullable = TableConvert.getNullAble(rs.getString(7));
            boolean ifPrimKey = TableConvert.ifPrimkey(rs.getString(8));
            // type = getType(type, precision, scale);
            type = db2JavaType(type, precision, scale);
            ColumnData cd = new ColumnData();
            cd.setColumnName(name);
            cd.setDataType(type);
            cd.setColumnType(rs.getString(2));
            cd.setColumnComment(comment);
            cd.setPrecision(precision);
            cd.setScale(scale);
            cd.setCharmaxLength(charmaxLength);
            cd.setNullable(nullable);
            // formatFieldClassType(cd);
            cd.setIfPrimKey(ifPrimKey);
            columnList.add(cd);
        }
        this.argv = str.toString();
        this.method = getset.toString();
        // DBUtils.free(rs, ps, con);
        return columnList;
    }
    
    public String db2JavaType(String dataType, String precision, String scale)
    {
        dataType = dataType.toLowerCase();
        if (dataType.contains("char"))
            dataType = "String";
        else if (dataType.contains("int"))
            dataType = "Integer";
        else if (dataType.contains("float"))
            dataType = "Float";
        else if (dataType.contains("double"))
            dataType = "Double";
        else if (dataType.contains("number"))
        {
            if ((StringUtils.isNotBlank(scale)) && (Integer.parseInt(scale) > 0))
                dataType = "BigDecimal";
            else if ((StringUtils.isNotBlank(precision)) && (Integer.parseInt(precision) > 6))
                dataType = "Long";
            else
                dataType = "Integer";
        }
        else if (dataType.contains("decimal"))
            dataType = "BigDecimal";
        else if (dataType.contains("date"))
            dataType = "Date";
        else if (dataType.contains("time"))
            // dataType = "java.sql.Timestamp";
            dataType = "Date";
        else if (dataType.contains("clob"))
            dataType = "Clob";
        else
        {
            dataType = "Object";
        }
        return dataType;
    }
    
    public String getTablesNameToClassName(String tableName)
    {
        String[] split = tableName.split("_");
        if (split.length > 1)
        {
            StringBuffer sb = new StringBuffer();
            for (int i = 0; i < split.length; i++)
            {
                String tempTableName =
                    split[i].substring(0, 1).toUpperCase() + split[i].substring(1, split[i].length());
                sb.append(tempTableName);
            }
            
            return sb.toString();
        }
        String tempTables = split[0].substring(0, 1).toUpperCase() + split[0].substring(1, split[0].length());
        return tempTables;
    }
    
    public Map<String, Object> getAutoCreateSql(String tableName)
        throws Exception
    {
        Map sqlMap = new HashMap();
        List<ColumnData> columnDatas = getColumnDatas(tableName);
        String columns = getColumnSplit(columnDatas);
        String[] columnList = getColumnList(columns);
        String columnFields = getColumnFields(columns);
        String insert = "insert into " + tableName + "(" + columns.replaceAll("\\|", ",") + ")\n values(#{"
            + columns.replaceAll("\\|", "},#{") + "})";
        String update = getUpdateSql(tableName, columnList);
        String updateSelective = getUpdateSelectiveSql(tableName, columnDatas);
        String selectById = getSelectByIdSql(tableName, columnList);
        String delete = getDeleteSql(tableName, columnList);
        sqlMap.put("columnList", columnList);
        sqlMap.put("columnFields", columnFields);
        sqlMap.put("insert", insert.replace("#{createTime}", "now()").replace("#{updateTime}", "now()"));
        sqlMap.put("update", update.replace("#{createTime}", "now()").replace("#{updateTime}", "now()"));
        sqlMap.put("delete", delete);
        sqlMap.put("updateSelective", updateSelective);
        sqlMap.put("selectById", selectById);
        return sqlMap;
    }
    
    public String getDeleteSql(String tableName, String[] columnsList)
        throws SQLException
    {
        StringBuffer sb = new StringBuffer();
        sb.append("delete ");
        sb.append("\t from ").append(tableName).append(" where ");
        sb.append(columnsList[0]).append(" = #{").append(columnsList[0]).append("}");
        return sb.toString();
    }
    
    public String getUpdateSql(String tableName, String[] columnsList)
        throws SQLException
    {
        StringBuffer sb = new StringBuffer();
        
        for (int i = 1; i < columnsList.length; i++)
        {
            String column = columnsList[i];
            if (!"CREATETIME".equals(column.toUpperCase()))
            {
                if ("UPDATETIME".equals(column.toUpperCase()))
                    sb.append(column + "=now()");
                else
                {
                    sb.append(column + "=#{" + column + "}");
                }
                if (i + 1 < columnsList.length)
                    sb.append(",");
            }
        }
        String update =
            "update " + tableName + " set " + sb.toString() + " where " + columnsList[0] + "=#{" + columnsList[0] + "}";
        return update;
    }
    
    private String getDbNameByurl(String url)
    {
        
        if (url == null || url.equals(""))
        {
            return null;
        }
        String dbName = url.substring(url.lastIndexOf("/") + 1);
        int len = dbName.indexOf("?");
        if (len > 0)
        {
            dbName = dbName.substring(0, len);
        }
        return dbName;
    }
    
    public String getColumnSplit(List<ColumnData> columnList)
        throws SQLException
    {
        StringBuffer commonColumns = new StringBuffer();
        for (ColumnData data : columnList)
        {
            commonColumns.append(data.getColumnName() + "|");
        }
        return commonColumns.delete(commonColumns.length() - 1, commonColumns.length()).toString();
    }
    
    public String[] getColumnList(String columns)
        throws SQLException
    {
        String[] columnList = columns.split("[|]");
        return columnList;
    }
    
    public String getUpdateSelectiveSql(String tableName, List<ColumnData> columnList)
        throws SQLException
    {
        StringBuffer sb = new StringBuffer();
        ColumnData cd = (ColumnData)columnList.get(0);
        sb.append("\t<trim  suffixOverrides=\",\" >\n");
        for (int i = 1; i < columnList.size(); i++)
        {
            ColumnData data = (ColumnData)columnList.get(i);
            String columnName = data.getColumnName();
            sb.append("\t<if test=\"").append(columnName).append(" != null ");
            
            if ("String" == data.getDataType())
            {
                sb.append(" and ").append(columnName).append(" != ''");
            }
            sb.append(" \">\n\t\t");
            sb.append(columnName + "=#{" + columnName + "},\n");
            sb.append("\t</if>\n");
        }
        sb.append("\t</trim>");
        String update = "update " + tableName + " set \n" + sb.toString() + " where " + cd.getColumnName() + "=#{"
            + cd.getColumnName() + "}";
        return update;
    }
    
    public String getSelectByIdSql(String tableName, String[] columnsList)
        throws SQLException
    {
        StringBuffer sb = new StringBuffer();
        sb.append("select <include refid=\"Base_Column_List\" /> \n");
        sb.append("\t from ").append(tableName).append(" where ");
        sb.append(columnsList[0]).append(" = #{").append(columnsList[0]).append("}");
        return sb.toString();
    }
    
    public String getColumnFields(String columns)
        throws SQLException
    {
        String fields = columns;
        if ((fields != null) && (!"".equals(fields)))
        {
            fields = fields.replaceAll("[|]", ",");
        }
        return fields;
    }
    
    
    
    public static void main(String args[])
    {
        String url = "jdbc:mysql://localhost:3308/jeecg?useUnicode=true&amp;characterEncoding=utf-8";
        /*
         * MysqlTableInfo t = new MysqlTableInfo(); try { t.setConnInfo(url, "root", ""); } catch (SQLException e) { //
         * TODO Auto-generated catch block e.printStackTrace(); } System.out.println(t.getDbNameByurl(url)); String
         * tableName = "test_user2"; try { Map auto = t.getAutoCreateSql(tableName); System.out.println(auto); } catch
         * (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }
         */
    }
    
}
